package com.gjy.sql.oracle.oa;

import com.gjy.sql.oracle.OracleUtil;
import com.gjy.sql.oracle.domain.WorkFlowMonitor;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * 流程类
 *
 * @author 宫静雨
 * @version 1.0
 * @since 2023-09-20 14:30:31
 */
public class WorkFlowUtil {

    @SuppressWarnings("SqlResolve")
    public static List<WorkFlowMonitor> workflowMonitorData() throws Exception {
        List<WorkFlowMonitor> list = new ArrayList<>();
        String sql = "SELECT\n" +
                "\t\twb.ID AS id,\n" +
                "\t\twb.workflowname AS name,\n" +
                "\t\twt.typename AS TYPE,\n" +
                "\t\th.SUBCOMPANYNAME AS company,\n" +
                "\t\twb.ISVALID AS valid,\n" +
                "\t\tSUM( CASE WHEN CURRENTNODETYPE = 3 THEN 1 ELSE 0 END ) AS archive,\n" +
                "\t\tSUM( CASE WHEN CURRENTNODETYPE != 3 THEN 1 ELSE 0 END ) AS other,\n" +
                "\t\tSUM( CASE WHEN CURRENTNODETYPE IS NOT NULL THEN 1 ELSE 0 END ) AS ownership \n" +
                "\tFROM\n" +
                "\t\tWORKFLOW_BASE wb\n" +
                "\t\tLEFT JOIN HRMSUBCOMPANY h ON wb.SUBCOMPANYID = h.ID\n" +
                "\t\tLEFT JOIN workflow_type wt ON wb.WORKFLOWTYPE = wt.id\n" +
                "\t\tLEFT JOIN WORKFLOW_REQUESTBASE wr ON wb.ID = wr.WORKFLOWID \n" +
                "\tGROUP BY\n" +
                "\t\twb.ID,\n" +
                "\t\twb.workflowname,\n" +
                "\t\twt.typename,\n" +
                "\t\th.SUBCOMPANYNAME,\n" +
                "\t\twb.ISVALID";
        Connection connection = OracleUtil.connection();
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            String id = rs.getString("id");
            String name = rs.getString("name");
            String type = rs.getString("type");
            String company = rs.getString("company");
            String valid = rs.getString("valid");
            int ownership = rs.getInt("ownership");
            int archive = rs.getInt("archive");
            int other = rs.getInt("other");
            WorkFlowMonitor monitor = new WorkFlowMonitor();
            monitor.setId(id);
            monitor.setName(name);
            monitor.setType(type);
            monitor.setCompany(company);
            monitor.setValid(validTransition(valid));
            monitor.setOwnership(ownership);
            monitor.setArchive(archive);
            monitor.setOther(other);
            list.add(monitor);
        }
        return list;
    }

    private static String validTransition(String valid) {
        String r;
        if (Objects.isNull(valid)) {
            r = "无效";
        } else {
            switch (valid) {
                case "1":
                    r = "有效";
                    break;
                case "2":
                    r = "测试";
                    break;
                case "3":
                    r = "历史版本";
                    break;
                default:
                    r = "无效";
                    break;
            }
        }
        return r;
    }

    public static void exportExcel(List<WorkFlowMonitor> list) {
        String s = LocalDate.now().toString();
        String file = "D:\\code\\java\\microservice_spc\\sql\\src\\main\\resources\\excel\\流程信息-" + s + ".xlsx";
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet();
            Row header = sheet.createRow(0);
            header.createCell(0).setCellValue("路径ID");
            header.createCell(1).setCellValue("路径名称");
            header.createCell(2).setCellValue("路径类型");
            header.createCell(3).setCellValue("所属机构");
            header.createCell(4).setCellValue("流程状态");
            header.createCell(5).setCellValue("发生数量");
            header.createCell(6).setCellValue("归档数量");
            header.createCell(7).setCellValue("在途数量");

            for (int i = 0; i < list.size(); i++) {
                Row container = sheet.createRow(i + 1);
                container.createCell(0).setCellValue(list.get(i).getId());
                container.createCell(1).setCellValue(list.get(i).getName());
                container.createCell(2).setCellValue(list.get(i).getType());
                container.createCell(3).setCellValue(list.get(i).getCompany());
                container.createCell(4).setCellValue(list.get(i).getValid());
                container.createCell(5).setCellValue(list.get(i).getOwnership());
                container.createCell(6).setCellValue(list.get(i).getArchive());
                container.createCell(7).setCellValue(list.get(i).getOther());
            }

            workbook.write(Files.newOutputStream(Paths.get(file)));
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}
